package pers.karl.export.data;

import lombok.extern.slf4j.Slf4j;
import pers.karl.export.database.enums.OracleDataTypeEnum;
import pers.karl.export.model.ColumnInfo;
import pers.karl.export.model.TableInfo;
import pers.karl.export.util.OracleUtils;
import pers.karl.export.util.SqlUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * 把数据库中的表结构导出word中
 * @author MOSHUNWEI
 * @version 1.0
 */
@Slf4j
public class OracleDataUtils {

	/**
	 * 获取表信息
	 * @param ip
	 * @param port
	 * @param serviceId
	 * @param user
	 * @param password
	 * @return
	 * @throws SQLException
	 */
	public static List<TableInfo> listTable(String ip, int port, String serviceId, String user, String password) throws SQLException {
		List<TableInfo> tableList;
		Connection connection = null;
		try {
			// 建立连接
			connection = OracleUtils.getConnection(ip,port,serviceId, user,password);

			// 获取表名列表
			String sqlTableList = "SELECT table_name, comments AS table_comment FROM user_tab_comments WHERE table_type = 'TABLE' order by TABLE_NAME";
			tableList = getTableName(connection, sqlTableList);

			// 获取每张表的列信息
			tableList = listTableInfo(connection, tableList);
		} finally {
			//关闭连接
			SqlUtils.closeConnection(connection);
		}
		return tableList;
	}

	public static List<TableInfo> listTableInfo(Connection connection, List<TableInfo> tableList) throws SQLException {

		String sqlOracleTableStructure = "select t.column_name, c.data_type,c.data_length,c.data_precision,c.data_scale,c.nullable as is_nullable,c.data_default,t.comments"
				+ ",("
				+ "select 'Y' AS IS_PRI_KEY "
				+ "from user_constraints constraint "
				+ "inner join user_cons_columns columns on constraint.TABLE_NAME = columns.TABLE_NAME and constraint.CONSTRAINT_NAME = columns.CONSTRAINT_NAME "
				+ "where constraint.TABLE_NAME = c.TABLE_NAME and columns.COLUMN_NAME = c.COLUMN_NAME and constraint.CONSTRAINT_TYPE = 'P'"
				+ " ) AS is_priv"
				+  " from user_tab_columns c"
				+  " inner join user_col_comments t on c.TABLE_NAME = t.TABLE_NAME and c.COLUMN_NAME = t.COLUMN_NAME"
				+  " where c.TABLE_NAME = '%s'"
				+  " ORDER BY c.column_id";

		for(TableInfo tableInfo : tableList) {

			String sql = String.format(sqlOracleTableStructure, tableInfo.getTableName());
			log.info("query table column: {}", tableInfo.getTableName());

			List<ColumnInfo> columnInfoList = listColumnInfo(connection, sql);
			tableInfo.setColumnInfoList(columnInfoList);
		}

		return tableList;
	}

	private static List<ColumnInfo> listColumnInfo(Connection connection, String sql) throws SQLException {
		ResultSet resultSet = SqlUtils.getResultSet(connection, sql);

		List<ColumnInfo> columnInfoList = new LinkedList<>();
		while(resultSet.next()) {
			// resultSet.getString 必须和查询顺序一致，否则会出现流已关闭错误

			String columnName = resultSet.getString("column_name");

			ColumnInfo columnInfo = new ColumnInfo();
			columnInfo.setColumnName(columnName);

			String dataType = resultSet.getString("data_type");
			columnInfo.setDataType(dataType);
			int dataLength = resultSet.getInt("data_length");
			columnInfo.setDataLength(dataLength);
			int dataPrecision = resultSet.getInt("data_precision");
			columnInfo.setDataPrecision(dataPrecision);
			int dataScale = resultSet.getInt("data_scale");
			columnInfo.setDataScale(dataScale);

			String columnType = OracleDataTypeEnum.getColumnType(dataType, dataLength,dataPrecision, dataScale);
			columnInfo.setColumnType(columnType);

			String isNullable = resultSet.getString("is_nullable");
			columnInfo.setNullAble("Y".equals(isNullable) ? "是" : "否");

			columnInfo.setDefaultValue(resultSet.getString("data_default"));

			String isPriKey = resultSet.getString("is_priv");
			columnInfo.setIsPriKey("Y".equals(isPriKey) ? "是" : "否");

			columnInfo.setColumnComment(resultSet.getString("comments"));
			columnInfoList.add(columnInfo);
		}
		return columnInfoList;
	}

	/**
     * 获取数据库的所有表名及表的信息
     * @return list
     */
    private static List<TableInfo> getTableName(Connection connection, String sql) throws SQLException {
		ResultSet rs = SqlUtils.getResultSet(connection, sql);
		if(!rs.next()) {
			return null;
		}

		List<TableInfo> list = new LinkedList<>();

		String[] excludeTablePrefix = {"ACT_","BAK"};
		String[] excludeTableSuffix = {};
		String[] excludeTableContain = {"TEMP","TMP"};

		TableInfo tableInfo;
		do {
			String tableName = rs.getString("table_name");

			boolean isIgnore = false;
			for (String tablePrefix : excludeTablePrefix) {
				if(tableName.startsWith(tablePrefix)) {
					isIgnore = true;
					break;
				}
			}

			if(!isIgnore) {
				for (String tableSuffix : excludeTableSuffix) {
					if(tableName.endsWith(tableSuffix)) {
						isIgnore = true;
						break;
					}
				}
			}

			if(!isIgnore) {
				for (String tableContain : excludeTableContain) {
					if(tableName.contains(tableContain)) {
						isIgnore = true;
						break;
					}
				}
			}

			if(isIgnore) {
				continue;
			}

			String tableComment = rs.getString("table_comment");

			tableInfo = new TableInfo();
			tableInfo.setTableName(tableName);
			tableInfo.setTableComment(tableComment);

			list.add(tableInfo);
		} while(rs.next());
		rs.close();

    	return list;
    }

}
